package com.zret.oasys.dbtools;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils {
	static SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	// 创建连接池对象
	private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();

	// 获得连接
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return conn;
	}

	// 通用查询1 将查询到结果依次封装成对象列表返回
	public static <T> List<T> exQuery(String sql, Class<T> clasz, Object... object) {
		List<T> list = new ArrayList<T>();
		// 1 获得连接
		Connection conn = getConnection();
		PreparedStatement ppsm = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		try {
			// 预处理
			ppsm = conn.prepareStatement(sql);
			// 插入占位符的条件
			if (object != null) {
				for (int i = 0; i < object.length; i++) {
					ppsm.setObject(i + 1, object[i]);
				}
			}
			// 执行查询得到果集
			rs = ppsm.executeQuery();
			// 判断是否是分页查询(特殊)
			if ("java.lang.Integer".equals(clasz.getName())) {
				if (rs.next()) {
					Integer i = rs.getInt(1);
					list.add((T) i);
					return list;
				}

			}
			// 获取当前结果集的元数据
			rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			// 获取结果集数据
			while (rs.next()) {
				// 通过java反射获取无参构造生成对象!
				T t = clasz.getConstructor().newInstance();
				// 通过for循环对对象赋值
				for (int i = 0; i < columnCount; i++) {
					// 根据元数据获得列名
					String columnName = rsmd.getColumnLabel(i + 1); 
					// 根据列名获取对应的值
					//Object columnValue = rs.getObject(columnName);
					Object columnValue = rs.getObject(i + 1);
					// 如果获取对象是日期格式需要进行类型转换!!!!!!不然报错!!!!!!
					if (columnValue instanceof Date) {
						columnValue = simple.format(columnValue);
					}
					// 将获取的列值封装进对象
					// PropertyUtils.setProperty(t, columnName, columnValue);
					BeanUtils.copyProperty(t, columnName, columnValue);
				}
				// 将对象添加至列表
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			JDBCUtils.release(rs, ppsm, conn);
		}

		return list;
	}

	/**
	 * 分页的通用方法，mysql数据库
	 * 
	 * @param <T>
	 * 
	 * @param sql
	 * @param cla
	 * @param page     当前页码
	 * @param pageSize 每页显示的记录数
	 * @param params
	 * @return
	 */
	public static <T> PageData<T> exQueryByPage(String sql, Class<T> cla, int page, int pageSize, Object... params) {
		// 问题:这里来加limit 还是传递过来之间就加好limit？
		// select .... from ... where ? ... limit ?,?

		String newsql = "select count(*) from (" + sql + ")  as t";
		// 如果传递的是object对象，查询操作得到就是单个结果
		int totalCount = (int) exQuery(newsql, Integer.class, params).get(0);

		// 可以加page的判断
		if (page < 1) {
			page = 1;
		}

		int start = (page - 1) * pageSize;
		sql = sql + " limit " + start + "," + pageSize;

		// 页面展示的数据集
		List<T> data = exQuery(sql, cla, params);

		PageData<T> pageData = new PageData<T>(data, page, totalCount, pageSize);

		return pageData;
	}

	// 执行增, 删, 改的函数
	public static int exUpdate(String sql, Object... params) {

		Connection conn = getConnection();

		PreparedStatement ppsm = null;
		int n = 0;
		try {
			ppsm = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ppsm.setObject(i + 1, params[i]);
				}
			}
			n = ppsm.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			// 释放资源
			release(ppsm, conn);
		}
		return n;
	}

	/**
	 * 事务管理的方法
	 * 
	 * @param sql    sql语句
	 * @param conn   连接对象
	 * @param params 参数列表
	 * @return 受影响的行
	 * @throws Exception
	 */
	public static int exUpdate(String sql, Connection conn, Object... params) throws Exception {
		PreparedStatement pstmt = null;
		int n = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			// ?的个数不确定
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			// 打印pstmt对象
			System.out.println("pstmt:" + pstmt);
			// 4.执行sql,executeUpdate()返回的是受影响的行
			n = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException("数据库操作异常");
		} finally {
			closeAll(null, pstmt, null);
		}
		return n;
	}
	
	/**
	 * 事务处理操作,稍难
	 * 
	 * @param tran
	 * @return
	 */
	public static Object transaction(ITransaction tran) {
		Connection conn = getConnection();
		try {
			conn.setAutoCommit(false);
			Object obj = tran.execute(conn);
			conn.commit();
			return obj;
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException("回滚失败!", e1);
			}
			throw new RuntimeException("事务执行失败", e);
		} finally {
			closeAll(null, null, conn);
		}
	}

	// 事务相关的!!!!!(终极版半成品)
	// 执行增, 删, 改的函数
	public static boolean exUpdate(List<String> sqls, List<List<Object>> params) {
		// 获取连接对象(因为统一在这个函数执行, 不会出现连接对象不统一情况)
		Connection conn = getConnection();
		PreparedStatement ppsm = null;
		boolean flag = true;
		int n = 0;
		try {
			//// 1 连接对象有一个方法,设置事务不自动提交
			conn.setAutoCommit(false);
			// 遍历所map, 插入跳进, 并执行sql语句
			for (int j = 0; j < sqls.size(); j++) {
				// 与处理对象
				ppsm = conn.prepareStatement(sqls.get(j));
				// 插入条件
				if (params != null) {
					if (params.get(j) != null) {
						for (int i = 0; i < params.get(j).size(); i++) {
							ppsm.setObject(i + 1, params.get(j).get(i));
						}
					}
				}
				// 执行sql语句
				n = ppsm.executeUpdate();
				if (n <= 0) {
					flag = false;
					break;
				}
			}

			if (flag) {
				System.out.println("事务提交成功");
				// 事务提交
				conn.commit();
			} else {
				// 事务回滚
				System.out.println("事务失败自动回滚!");
				conn.rollback();
			}

		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("事务失败自动回滚!");
			flag = false;
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			// 释放资源
			release(ppsm, conn);
		}
		return flag;
	}

	// 资源释放
	public static void release(PreparedStatement ppsm, Connection conn) {
		if (ppsm != null) {
			try {
				ppsm.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
			ppsm = null;
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
			conn = null;
		}
	}

	// 资源释放
	public static void release(ResultSet rs, PreparedStatement ppsm, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
			rs = null;
		}

		if (ppsm != null) {
			try {
				ppsm.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
			ppsm = null;
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
			conn = null;
		}
	}
	
	//资源释放
	/**
	 * 释放资源
	 * 
	 * @param rs    结果集对象
	 * @param pstmt 预处理对象
	 * @param conn  连接对象
	 */
	public static void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			throw new RuntimeException("释放资源异常", e);
		}
	}
}
